<%@ page language="java" import="org.json.*,java.sql.*,com.microsoft.sqlserver.jdbc.*,java.io.*,web.rec.*" 
contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"  %>

<% 
	Connection conn = ResourceTool.getConnection();
%> 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
<script type="text/javascript" src="script/jquery-1.6.3.min.js"></script>
<script type="text/javascript" src="script/form_script.js"></script>
</head>
<body>
          <%
			try {
			// Create the prepared statement and use it to
			// INSERT the student attrs INTO the Student table.
			Statement stmt = conn.createStatement();
			String action = request.getParameter("action");
			%>
			<table>
			<tr>
				<th>class id</th>
				<th>title</th>
				<th>course num</th>
				<th>year</th>
				<th>quarter</th>
				<th>units</th>
				<th>grade</th>
			</tr>
			<%
			String student_id = request.getParameter("student_id");
			if (action != null && action.equals("submit"))
			{
				String sql = "select * from Class, Section, StudentTakingSection where "
				   	       + "Class.class_id = Section.class_id and "
						   + "Section.section_id = StudentTakingSection.section_id and "
						   + "StudentTakingSection.student_id = '"+ student_id + "'"
						   + " order by year, season";
				System.out.println(sql);
				ResultSet rs = stmt.executeQuery(sql);
				boolean firstQuarter = true;
				int yearOfLastRecord = 0;
				String seasonOfLastRecord = null;
				int year = 0;
				String season;
				int totalUnits = 0;
				float totalGrade = 0;
				int quarterUnits = 0;
				float quarterGrade = 0;
				String letter_grade = null;
				float grade;
				int units;
				float gpa;
				float cumulativeGpa;
				while(rs.next())
				{
					year = rs.getInt("year");
					season = rs.getString("season");
					letter_grade =  rs.getString("grade");
					units = rs.getInt("units");
					if(!firstQuarter)
					{
						if((year != yearOfLastRecord)||!(season.equals(seasonOfLastRecord)))
						{
							if(quarterUnits != 0)
							{
								gpa = quarterGrade/quarterUnits;
							}
							else
							{
								gpa = 0;
							}
							quarterUnits = 0;
							quarterGrade = 0;
							%>
							<tr>
							<td>GPA of the above quarter</td>
							<td><%= gpa %></td>
							</tr>
							<%
						}
					}
					else
					{
						firstQuarter = false;
					}
					yearOfLastRecord = year;
					seasonOfLastRecord = season;
			%>
			<tr>
				<td><%= rs.getString("class_id") %></td>
				<td><%= rs.getString("title") %></td>
				<td><%= rs.getString("course_num") %></td>
				<td><%= year %></td>
				<td><%= season %></td>
				<td><%= units %></td>
				<td><%= letter_grade %></td>
			</tr>
			<%
				// assume incomplete courses and S/U courses have a minus grade
				if(!letter_grade.toLowerCase().equals("s")
				  &&!letter_grade.toLowerCase().equals("u")
				  &&!letter_grade.toLowerCase().equals("in"))
				{
					grade = Float.parseFloat(letter_grade);
					quarterUnits += units;
					quarterGrade += grade * units;
					totalUnits += units;
					totalGrade += grade * units;					
				}
			}
			if(quarterUnits != 0)
			{
				gpa = quarterGrade/quarterUnits;
			}
			else
			{
				gpa = 0;
			}
			%>
			<tr>
			<td>GPA of the above quarter</td>
			<td><%= gpa %></td>
			</tr>
			<%
			if(totalUnits != 0)
			{
				cumulativeGpa =  totalGrade/totalUnits;
			}
			else
			{
				cumulativeGpa = 0;
			}
			%>
			<tr>
			<td>Cumulative GPA: </td>
			<td><%= cumulativeGpa %></td>
			</tr>
			<%
		}
	} catch (SQLException sqle) {
		out.println(sqle.getMessage());
	} catch (Exception e) {
		out.println(e.getMessage());
	}    
            %>
      </table>
</body>
</html>